﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Xml.Linq;
using System.Text;
using DataAccess.SQL;
using System.Collections.Generic;
using DataAccess.Oracle;
using System.Data.SqlClient;
using CommonLib;
using Framework.Caching;
using Framework;
using Entities;
using Entities.System;

namespace BLL.System
{
    /// <summary>功能菜单
    /// 
    /// </summary>
    public class MenuBO : BusinessBase
    {
        private DataTable TFunction
        {
            set { CacheHelper<DataTable>.Insert(new CacheKey(typeof(DataTable), "function"), value); }
            get { return CacheHelper<DataTable>.GetCache(new CacheKey(typeof(DataTable), "function")); }
        }

        /// <summary>获得所有父节点ＫＥＹ
        /// 
        /// </summary>
        /// <param name="funKeysAll"></param>
        /// <param name="key"></param>
        private void getParent(List<string> funKeysAll, string key)
        {
            DataView view = TFunction.DefaultView;
            view.RowFilter = "FUNCTION_KEY = " + key;
            if (view.Count > 0)
            {
                string pid = view[0]["PARENT_KEY"].ToString();
                if (!funKeysAll.Contains(pid)) 
                    funKeysAll.Add(pid);
                getParent(funKeysAll, pid);
            }
        }

        /// <summary>获得登陆用户可见菜单
        /// 
        /// 
        /// </summary>
        /// <param name="userkey"></param>
        /// <param name="IsAdmin"></param>
        /// <returns></returns>
        public DataTable getMyMenu(int userkey,bool IsAdmin) {
            //取得DataTable 
            StringBuilder SqlTxt = new StringBuilder();
            DataSet Ds = new DataSet();
            try
            {
                if (!IsAdmin)
                {
                    //所有菜单
                    if (TFunction == null)
                    {
                        Ds = SqlHelper.GetDataSet("select FUNC_TYPE,PARENT_KEY,FUNCTION_NAME,FUNCTION_KEY FROM SBRM_FUNCTION order by sequence ");
                        TFunction = Ds.Tables[0];
                    }

                    List<string> funKeysAll = new List<string>();
                    // 只根据VIEW权限来过滤要显示哪些菜
                    SqlTxt.AppendFormat(@"
SELECT DISTINCT F.FUNCTION_KEY
FROM SBRM_USER U 
INNER JOIN  SBRM_USER_GROUP UG ON U.USER_KEY = UG.USER_KEY 
INNER JOIN  SBRM_GROUP G ON UG.GROUP_KEY = G.GROUP_KEY 
INNER JOIN SBRM_GROUP_FUNCTION GF ON UG.GROUP_KEY = GF.GROUP_KEY 
INNER JOIN SBRM_FUNCTION F ON F.FUNCTION_KEY = GF.FUNCTION_KEY 
WHERE U.ACTIVE = 'Y' AND G.ACTIVE = 'Y' AND U.USER_KEY = {0} AND GF.ACTION_ID = 'VIEW'
                                    ", userkey);

                    DataTable tblLeaf = SqlHelper.GetDataSet(SqlTxt.ToString()).Tables[0];
                    //没有任何菜单查看权限
                    if (tblLeaf.Rows.Count == 0) return null;
                    //获取相关菜单的根及其节点
                    foreach (DataRow row in tblLeaf.Rows)
                    {
                        getParent(funKeysAll, row[0].ToString());
                        funKeysAll.Add(row[0].ToString());
                    }
                    string keystring = string.Empty;
                    foreach (var key in funKeysAll)
                    {
                        keystring += string.Format("'{0}',", key);

                    }
                    keystring = keystring.Substring(0, keystring.Length - 1);
                    Ds = SqlHelper.GetDataSet(string.Format("SELECT DISTINCT F.* FROM SBRM_FUNCTION F where function_key in ({0})", keystring));
                    Ds.Dispose();
                    return Ds.Tables[0];

                }
                else
                {
                    //超级管理员显示所有
                    SqlTxt.AppendFormat(@"SELECT DISTINCT F.* FROM SBRM_FUNCTION F  ");
                    Ds = SqlHelper.GetDataSet(SqlTxt.ToString());
                    Ds.Dispose();
                    return Ds.Tables[0];
                }
            }
            catch (Exception e)
            {
                return  null;
            }
        }

        /// <summary>all menu
        /// 
        /// </summary>
        /// <returns></returns>
        public string getMenu()
        {
            List<Menu> lst = new List<Menu>();
            string strSql = @"SELECT FUNCTION_KEY,FUNCTION_NAME,PARENT_KEY,SEQUENCE,FUNC_TYPE,URL FROM SBRM_FUNCTION  ";

            try
            {
                DataSet Ds = SqlHelper.GetDataSet(strSql);
                DataTable dt = Ds.Tables[0];
                DataView view = dt.DefaultView;
                view.RowFilter = "PARENT_KEY = 0";
                DataView viewF = new DataView(dt);
                viewF.RowFilter = " FUNC_TYPE = '0'";
                List<string> folder = new List<string>();

                foreach (DataRowView row in viewF)
                {
                    folder.Add(row["FUNCTION_KEY"].ToString());
                }

                foreach (DataRowView row in view)
                {
                    Menu item = new Menu
                    {
                        id = row["FUNCTION_KEY"] == DBNull.Value ? "" : row["FUNCTION_KEY"].ToString(),
                        text = row["FUNCTION_NAME"] == DBNull.Value ? "" : row["FUNCTION_NAME"].ToString(),
                        attributes = new MenuAttr()
                        {
                            URL = row["URL"] == DBNull.Value ? "" : row["URL"].ToString(),
                            SEQUENCE = Convert.ToInt32(row["SEQUENCE"]),
                            PARENT_KEY = row["PARENT_KEY"] == DBNull.Value ? "" : row["PARENT_KEY"].ToString(),
                            FUNC_TYPE = Convert.ToInt32(row["FUNC_TYPE"])
                        },
                        children = new List<Menu>()

                    };
                    DataView childview = new DataView(dt);
                    childview.RowFilter = string.Format("PARENT_KEY = '{0}'", row["FUNCTION_KEY"]);
                    childview.Sort = "SEQUENCE ";

                    if (childview.Count > 0)
                    {
                        foreach (DataRowView child in childview)
                        {
                            appendMenu(child, dt, item);
                        }
                    }

                    item.attributes.URL = folder.ToArray().ToJson();
                    lst.Add(item);
                }
                Ds.Dispose();
                return lst.ToJson();
            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();
            }
        }

        private void appendMenu(DataRowView row, DataTable tbl, Menu parent)
        {
            DataView view = new DataView(tbl);
            view.RowFilter = string.Format("PARENT_KEY = '{0}'", row["FUNCTION_KEY"]);
            view.Sort = "SEQUENCE ";
            Menu item = new Menu
            {
                id = row["FUNCTION_KEY"] == DBNull.Value ? "" : row["FUNCTION_KEY"].ToString(),
                text = row["FUNCTION_NAME"] == DBNull.Value ? "" : row["FUNCTION_NAME"].ToString(),

                attributes = new MenuAttr()
                {
                    URL = row["URL"] == DBNull.Value ? "" : row["URL"].ToString(),
                    SEQUENCE = Convert.ToInt32(row["SEQUENCE"]),
                    PARENT_KEY = row["PARENT_KEY"] == DBNull.Value ? "" : row["PARENT_KEY"].ToString(),
                    FUNC_TYPE = Convert.ToInt32(row["FUNC_TYPE"])
                },
                children = new List<Menu>()

            };

            if (view.Count > 0)
            {
                foreach (DataRowView child in view)
                {
                    appendMenu(child, tbl, item);
                }

            }
            parent.children.Add(item);
        }

        /// <summary>上移
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string upMenu(HttpContext context)
        {
            string strSql = string.Empty;
            List<string> sqlArr = new List<string>();
            Menu menu;
            menu = JsonHelper.FromJson<Menu>(context.Request.Form["data"]);
            if (!string.IsNullOrEmpty(menu.attributes.PARENT_KEY))
            {
                strSql = "update sbrm_function set SEQUENCE = SEQUENCE + 1 where PARENT_KEY = '{0}' and SEQUENCE = {1}";
                strSql = string.Format(strSql, menu.attributes.PARENT_KEY, menu.attributes.SEQUENCE - 1);
                sqlArr.Add(strSql);
            }
            sqlArr.Add(string.Format("update sbrm_function set SEQUENCE = SEQUENCE - 1 where FUNCTION_KEY = '{0}' ", menu.id));
            if (SqlHelper.ExecuteSqlTran(sqlArr))
                return (new Status { Success = true }).ToJson();
            else
                return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>下移
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string downMenu(HttpContext context)
        {
            string strSql = string.Empty;
            List<string> sqlArr = new List<string>();
            Menu menu;
            //上下兑换SEQUENCE
            menu = JsonHelper.FromJson<Menu>(context.Request.Form["data"]);
            if (!string.IsNullOrEmpty(menu.attributes.PARENT_KEY))
            {
                strSql = "update  sbrm_function set SEQUENCE = SEQUENCE - 1  where  PARENT_KEY = '{0}' and SEQUENCE = {1}";
                strSql = string.Format(strSql, menu.attributes.PARENT_KEY, menu.attributes.SEQUENCE + 1);
                sqlArr.Add(strSql);
            }
            sqlArr.Add(string.Format("update sbrm_function set SEQUENCE = SEQUENCE + 1 where FUNCTION_KEY = '{0}' ", menu.id));
            if (SqlHelper.ExecuteSqlTran(sqlArr)) 
                return (new Status { Success = true }).ToJson();
            else
                return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>删除
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string delMenu(HttpContext context)
        {
            string strSql = string.Empty;
            string Msg = string.Empty;
            List<string> sqlArr = new List<string>();
            //删除
            if (!string.IsNullOrEmpty(context.Request.Form["PARENT_KEY"]))
            {
                strSql = "update  sbrm_function set SEQUENCE = SEQUENCE - 1 where PARENT_KEY = '{0}' and SEQUENCE > {1}";
                strSql = string.Format(strSql, context.Request.Form["PARENT_KEY"], context.Request.Form["SEQUENCE"]);
                sqlArr.Add(strSql);
            }
            sqlArr.Add(string.Format("delete  from  sbrm_function   where  FUNCTION_KEY='{0}' ", context.Request.Form["id"]));
            if (SqlHelper.ExecuteSqlTran(sqlArr))
            {
                int result = insertLog(context.Session["user"] as User, context.Request["FUNCTION_NAME"], context.Request["URL"],
                     context.Request["PARENT_KEY"], context.Request["id"], context.Request["FUNC_TYPE"], "DELETE",null);
                return (new Status { Success = true }).ToJson();
            }
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>记录日志
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <param name="funcName"></param>
        /// <param name="url"></param>
        /// <param name="parentkey"></param>
        /// <param name="funcKey"></param>
        /// <param name="funcType"></param>
        /// <returns></returns>
        private int insertLog(User user, string funcName, string url, string parentkey,
            string funcKey, string funcType, string status, SqlTransaction trans)
        {
            string sql = string.Format(@"
insert into  sbrm_function_log
(FUNCTION_KEY, FUNCTION_NAME, PARENT_KEY  ,URL ,
UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME ,UPDATE_DATE, STATUS) 
                                values 
({0},@FUNCTION_NAME,{1}, @URL ,
@UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME ,GETDATE(),'{2}')",
                                funcKey, parentkey, status);
            if (trans != null)
                return SqlHelper.ExecuteNonQuery(trans, sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@FUNCTION_NAME" , Value= funcName  },
                    new SqlParameter{ ParameterName="@URL" , Value= url==null ? "" : url   },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                });
            else
                return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@FUNCTION_NAME" , Value= funcName  },
                    new SqlParameter{ ParameterName="@URL" , Value= url   },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                });
        }

        /// <summary>新增
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string addMenu(HttpContext context)
        {
            string strSql = string.Empty;
            bool result = true;
            List<string> sqlArr = new List<string>();
            Menu menu = JsonHelper.FromJson<Menu>(context.Request.Form["data"]);

            //新增
            int FUNC_TYPE = 0;
            switch (context.Request["type"])
            {
                case "rpt":
                    FUNC_TYPE = 1;
                    break;
                case "folder":
                    FUNC_TYPE = 0;
                    break;
            }

            User user = context.Session["user"] as User;
            strSql = @"insert into sbrm_function
(FUNCTION_NAME, FUNC_TYPE , PARENT_KEY,   SEQUENCE, URL,
UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME,UPDATE_DATE)
values 
(@FUNCTION_NAME, @FUNC_TYPE, @PARENT_KEY, @SEQUENCE, @URL,
@UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME,GETDATE())
select @@identity as ID ";

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                    new SqlParameter{ ParameterName="@FUNCTION_NAME" , Value=menu.text  },
                    new SqlParameter{ ParameterName="@FUNC_TYPE" , Value=FUNC_TYPE   },
                    new SqlParameter{ ParameterName="@PARENT_KEY" , Value=menu.attributes.PARENT_KEY  },
                    new SqlParameter{ ParameterName="@SEQUENCE" , Value=menu.attributes.SEQUENCE  },
                    new SqlParameter{ ParameterName="@URL" , Value=menu.attributes.URL==null ? "" : menu.attributes.URL  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }};
                    cmd.Parameters.AddRange(parms);
                    //cmd.ExecuteNonQuery();
                    int newID = Convert.ToInt32(cmd.ExecuteScalar());

                    //插入日志
                    insertLog(user, menu.text, menu.attributes.URL,
                         menu.attributes.PARENT_KEY, newID.ToString(), menu.attributes.FUNC_TYPE.ToString(), "ADD", tx);
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            if (result)
            {
                return (new Status { Success = true }).ToJson();
            }
            else 
                return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        /// <summary>编辑菜单|目录
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string editMenu(HttpContext context)
        {
            string strSql = string.Empty;
            List<string> sqlArr = new List<string>();
            Menu menu = JsonHelper.FromJson<Menu>(context.Request.Form["data"]);
            int result = 0;

            //编辑
            User user = context.Session["user"] as User;

            switch (context.Request["type"])
            {
                case "rpt":
                    strSql = @"update sbrm_function 
set FUNCTION_NAME = @FUNCTION_NAME, URL = @URL, UPDATE_USER_KEY = @UPDATE_USER_KEY,
UPDATE_USER_ID = @UPDATE_USER_ID,UPDATE_USER_NAME = @UPDATE_USER_NAME,UPDATE_DATE = GETDATE()
where FUNCTION_KEY = @FUNCTION_KEY ";

                    result = SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, new SqlParameter[] {
                        new SqlParameter{ ParameterName="@FUNCTION_NAME" , Value=menu.text  },
                        new SqlParameter{ ParameterName="@URL", Value=menu.attributes.URL  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  },
                        new SqlParameter{ ParameterName="@FUNCTION_KEY" , Value=menu.id }}
                        );
                    break;
                case "folder":
                    strSql = @"update sbrm_function 
set FUNCTION_NAME = @FUNCTION_NAME, UPDATE_USER_KEY = @UPDATE_USER_KEY,
UPDATE_USER_ID = @UPDATE_USER_ID,UPDATE_USER_NAME = @UPDATE_USER_NAME,UPDATE_DATE = GETDATE()
where  FUNCTION_KEY = @FUNCTION_KEY ";
                    result = SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, new SqlParameter[] {
                        new SqlParameter{ ParameterName="@FUNCTION_NAME" , Value=menu.text  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  },
                        new SqlParameter{ ParameterName="@FUNCTION_KEY" , Value=menu.id } }
                        );
                    break;
            }

            if (result > 0)
                return (new Status { Success = true }).ToJson();
            else
                return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>sub menu
        /// 
        /// </summary>
        /// <returns></returns>
        public string getSubMenu()
        {
            List<FUNCTION> lst = new List<FUNCTION>();

            string strSql = @"SELECT FUNCTION_KEY, FUNCTION_NAME FROM SBRM_FUNCTION WHERE url is not null order by parent_key, sequence ";
            try
            {
                lst = SqlHelper.ExecuteList<FUNCTION>(strSql, CommandType.Text);
                return lst.ToJson();
            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }
        }
    }

}
